﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Collections.Generic;
using nsBaseClass;
using log4net;
using BMWSEMCRM.Model;
using BMWSEMCRM.AppUtils;
using System.Collections;

namespace BMWSEMCRM.Database
{
    public class DBAccess
    {
        static readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //Constructing log
        public static void InsertOppoturnity(List<Oppoturnity> list)
        {
            log.Debug("Insert to DB starting....");
            clsSqlFactory hSql = new clsSqlFactory(); //Constructing a sqlfactory class for sql usage 
            try
            {
                string sql = "INSERT INTO LEADIMP(SITEID,LNAME,FNAME,ADDR2,ADDR2E,PO,POCD,WTEL,EMAIL,CGRPID,ORGTYPE,CTRCD,LANG,SALUTCODE,AGRP,BGROUP,EXTSYSID,COMMCHANNEL,MAKE,MODEL,HEATLEVEL,LEADID,CREATIONCHANNEL,HEADER,PLANNED,BODY,ACTTYPE,MCAMPID,TYPE,_Created,ISCOMPANY,SMANID,VEHTYPE,TIMAKE,TIMODEL,country,MARKETGRP) VALUES {0} ";
                string endSql = "";
                foreach (var o in list)
                {
                    string temp = "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}',{27},'{28}',{29},{30},'{31}','{32}','{33}','{34}','{35}','{36}'),";
                    string[] pars = new string[]{StringUtils.SubstringMax(o.SITEID,3),StringUtils.SubstringMax(o.LNAME,200),StringUtils.SubstringMax(o.FNAME,100),StringUtils.SubstringMax(o.ADDR2,200),StringUtils.SubstringMax(o.ADDR2E,200),StringUtils.SubstringMax( o.PO,20),StringUtils.SubstringMax( o.POCD,8),StringUtils.SubstringMax( o.WTEL,20),StringUtils.SubstringMax(o.EMAIL,40), StringUtils.SubstringMax(o.CGRPID,6),StringUtils.SubstringMax(o.ORGTYPE,1),
                    StringUtils.SubstringMax(o.CTRCD,3),StringUtils.SubstringMax(o.LANG,3), StringUtils.SubstringMax(o.SALUTCODE,10),
                    StringUtils.SubstringMax(o.AGRP,5),StringUtils.SubstringMax(o.BGROUP,3), StringUtils.SubstringMax(o.EXTSYSID,32),StringUtils.SubstringMax(o.COMM_CHANNEL,10),
                    StringUtils.SubstringMax(o.MAKE,15),StringUtils.SubstringMax(o.MODEL,80),StringUtils.SubstringMax(o.HEAT_LEVEL,10),StringUtils.SubstringMax(o.LEAD_ID,50),
                    StringUtils.SubstringMax(o.CREATION_CHANNEL,10),StringUtils.SubstringMax(o.HEADER,250),o.PLANNED,o.BODY,StringUtils.SubstringMax(o.ACTTYPE,10),
                    string.IsNullOrEmpty(o.MCAMP_ID) ? "0" : o.MCAMP_ID,StringUtils.SubstringMax(o.TYPE,10),
                    string.IsNullOrEmpty(o._Created) ? "null" : string.Format("CONVERT (datetime, '{0}', 126)",o._Created),
                    o.ISCOMPANY ,StringUtils.SubstringMax(o.SMANID,5),string.IsNullOrEmpty(o.VEHTYPE)? "0" : o.VEHTYPE,StringUtils.SubstringMax(o.TIMAKE,15),
                    StringUtils.SubstringMax(o.TIMODEL,80),StringUtils.SubstringMax(o.COUNTRY,20), o.MARKETGRP};

                    temp = string.Format(temp, pars);
                    endSql += temp;
                    temp = "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}',{27},'{28}',{29},{30},'{31}','{32}','{33}','{34}','{35}','{36}'),";
                }
                endSql = endSql.Substring(0, endSql.Length - 1);
                sql = string.Format(sql, endSql);

                hSql.NewCommand(sql);
                int val = -1;
                hSql.ExecuteNonQuery(out val);
                hSql.Commit();

                log.Debug("Insert Rows Affected: " + val);
                hSql.Close();

                log.Debug("Insert to DB finished");

            }
            catch (Exception ex)
            {
                hSql.Rollback();
                hSql.Close();
                log.Error(ex.Message);
                throw ex;
            }
        }

        public static Hashtable GetSiteID(string LocalDealerID)
        {
            clsSqlFactory hSql = new clsSqlFactory(); //Constructing a sqlfactory class for sql usage 
            var objUtil = new clsBaseUtility();

            string sql = "SELECT C1 AS SITEID, c4 as SMANID FROM {0} WHERE C3 LIKE '{1}'";
            sql = string.Format(sql, objUtil.getTable("CORW"), LocalDealerID);

            Hashtable ht = new Hashtable();
            hSql.NewCommand(sql);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                string siteid = hSql.Reader.GetString(0);    //Read from readerstream to array of strings
                string smanid = hSql.Reader.GetString(1);
                ht.Add("SITEID", siteid);
                ht.Add("SMANID", smanid);

                if (siteid == String.Empty || siteid == String.Empty)    //Is a field empty?
                {
                    log.Error("A field in the database is empty or Null!");
                }
            }
            hSql.Close();
            return ht;
        }

        public static string GetActType(string type)
        {
            clsSqlFactory hSql = new clsSqlFactory(); //Constructing a sqlfactory class for sql usage 
            var objUtil = new clsBaseUtility();

            string sql = "SELECT C4 AS ACTTYPE FROM {0} WHERE C1 LIKE '{1}'";
            sql = string.Format(sql, objUtil.getTable("CORW"), type);

            string ret = string.Empty;
            hSql.NewCommand(sql);
            hSql.ExecuteReader();
            if (hSql.Read())
            {
                ret = hSql.Reader.GetString(0);
                if (ret == String.Empty)    //Is a field empty?
                {
                    log.Error("A field in the database is empty or Null!");
                    hSql.Close();
                    return string.Empty;
                }
                else
                {
                    hSql.Close();
                    return ret;
                }
            }
            else
            {
                hSql.Close();
                return string.Empty;
            }
               

        }



    }
}
